/*
 * To change this template, choose Tools | Templates and open the template in
 * the editor.
 */
package eypweb_bsc;

import java.net.URL;
import java.sql.*;
import java.util.ResourceBundle;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.event.EventType;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.geometry.Pos;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.input.MouseEvent;

/**
 *
 * @author Kohalmi Istvan
 */

// eszkozccsoportok rogziteset szolgalo kepernyo
public class InstrumentGroupRegistry implements Initializable {

    @FXML
    private Button btnNew;
    @FXML
    private Button btnMod;
    @FXML
    private Button btnCan;
    @FXML
    private Button btnRes;
    @FXML
    private Button btnCom;
    @FXML
    private Button btnEsc;
    @FXML
    private Label errorLabel;
    @FXML
    private TextField tfName;
    @FXML
    private TextField tfStart;
    @FXML
    private TextField tfEnd;
    @FXML
    private TableView<InstrumentGroup> masterData;

    public InstrumentGroupRegistry() {
        super();
    }

    @FXML
    private void handleNewButtonAction(ActionEvent event) {
        if (btnNew.getText().equals("Új elem"))
        {
            btnMod.setDisable(true);
            btnCom.setDisable(true);
            btnRes.setDisable(true);
            btnCan.setDisable(false);
            btnNew.setText("Rendben");
            tfName.clear();
            tfStart.clear();
            tfEnd.clear();
        }
        else
        {
            if (tfName.getText().isEmpty())
            {
                errorLabel.setText("A mezőket kötelező kitölteni!");
                return;
            }
            try{
                Date start = Date.valueOf(tfStart.getText());
                Date end = Date.valueOf(tfEnd.getText());
                if (start.after(end))
                {
                    errorLabel.setText("A kezdődátum nem lehet későbbi, mint a végdátum!");
                    return;
                }
            }
            catch(Exception e)
            {
                errorLabel.setText("A dátum nincs megfelelően formázva!");
                return;
            }
            InstrumentGroup instrument = new InstrumentGroup();
            instrument.setId(0);
            instrument.setName(tfName.getText());
            instrument.setStartDate(tfStart.getText());
            instrument.setEndDate(tfEnd.getText());
            instrument.setModified();
            masterData.getItems().add(instrument);
            btnMod.setDisable(false);
            btnRes.setDisable(false);
            btnCom.setDisable(false);
            btnNew.setText("Új elem");
        }
    }

    @FXML
    private void handleModifyButtonAction(ActionEvent event) {
        if (btnMod.getText().equals("Módosít")) {
            InstrumentGroup selectedItem = masterData.getSelectionModel().getSelectedItem();
            if (selectedItem != null) {
                tfName.setText(selectedItem.getName());
                tfStart.setText(selectedItem.getStartDate());
                tfEnd.setText(selectedItem.getEndDate());
                btnMod.setText("Rendben");
                btnNew.setDisable(true);
                btnCom.setDisable(true);
                btnRes.setDisable(true);
                btnCan.setDisable(false);
            }
        } else {
            if (tfName.getText().isEmpty())
            {
                errorLabel.setText("A mezőket kötelező kitölteni!");
                return;
            }
            try{
                Date start = Date.valueOf(tfStart.getText());
                Date end = Date.valueOf(tfEnd.getText());
                if (start.after(end))
                {
                    errorLabel.setText("A kezdődátum nem lehet későbbi, mint a végdátum!");
                    return;
                }
            }
            catch(Exception e)
            {
                errorLabel.setText("A dátum nincs megfelelően formázva!");
                return;
            }
            InstrumentGroup selectedItem = masterData.getSelectionModel().getSelectedItem();
            if (selectedItem != null) {
                selectedItem.setName(tfName.getText());
                selectedItem.setStartDate(tfStart.getText());
                selectedItem.setEndDate(tfEnd.getText());
                selectedItem.setModified();
                btnMod.setText("Módosít");
                btnNew.setDisable(false);
                btnCom.setDisable(false);
                btnRes.setDisable(false);
            }
        }
    }
    
    @FXML
    private void handleCancelButtonAction(ActionEvent event)
    {
        btnNew.setDisable(false);
        btnMod.setDisable(false);
        btnRes.setDisable(false);
        btnCom.setDisable(false);
        btnNew.setText("Új elem");
        btnMod.setText("Módosít");
        btnCan.setDisable(true);
    }

    @FXML
    private void handleResetButtonAction(ActionEvent event) {
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        ObservableList<InstrumentGroup> instruments = FXCollections.observableArrayList();
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());
            String instrumentQuery = "SELECT * FROM eszkoz_csoport WHERE ? BETWEEN hatalytol AND hatalyig";
            PreparedStatement stmt = con.prepareStatement(instrumentQuery);
            stmt.setDate(1, new Date(new java.util.Date().getTime()));
            ResultSet results = stmt.executeQuery();
            while (results.next()) {
                InstrumentGroup instrument = new InstrumentGroup();
                instrument.setId(results.getInt(1));
                instrument.setName(results.getNString(2));
                instrument.setStartDate(results.getDate(3).toString());
                instrument.setEndDate(results.getDate(4).toString());
                instruments.add(instrument);
            }
            con.close();
        } catch (SQLException sqle) {
            errorLabel.setText(sqle.getMessage());
        }
        masterData.getItems().clear();
        masterData.getItems().addAll(instruments);
    }
    
    @FXML
    private void handleCommitButtonAction(ActionEvent event){
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        ObservableList<InstrumentGroup> instruments = masterData.getItems();
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());
            String insertAction = "exec AddOrModifyInstrumentGroup ?,?,?,?";
            for (InstrumentGroup instr : instruments)
            {
                if (instr.getModified()){
                    PreparedStatement insertStmt = con.prepareStatement(insertAction);
                    insertStmt.setInt(1, instr.getId());
                    insertStmt.setNString(2, instr.getName());
                    insertStmt.setDate(3, Date.valueOf(instr.getStartDate()));
                    insertStmt.setDate(4, Date.valueOf(instr.getEndDate()));
                    insertStmt.executeUpdate();
                }
            }
            con.close();
            handleResetButtonAction(new ActionEvent());
        } catch (SQLException sqle) {
            errorLabel.setText(sqle.getMessage());
        }
    }

    @FXML
    private void handleEscapeButtonAction(ActionEvent event) {
        Director director = Director.getInstance();
        director.initializeMainMenu();
    }

    @FXML
    private void handleCSVButtonAction(ActionEvent event) {
        CSVHandler.exportInstrumentGroup(masterData.getItems());
    }

    @FXML
    private void handleXLSButtonAction(ActionEvent event) {
        XLSHandler.exportInstrumentGroup(masterData.getItems());
    }

    @FXML
    private void handleXMLButtonAction(ActionEvent event) {
        XMLHandler.exportInstrumentGroup(masterData.getItems());
    }
    
    @FXML
    private void handleRowSelect(MouseEvent event) {
        if (masterData.getSelectionModel().getSelectedItem() != null) {
            btnMod.setDisable(false);
        }
        else{
            btnMod.setDisable(true);
        }
    }

    public void initialize(URL url, ResourceBundle bundle) {
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        ObservableList<InstrumentGroup> instruments = FXCollections.observableArrayList();
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());
            String instrumentQuery = "SELECT * FROM eszkoz_csoport";
            PreparedStatement stmt = con.prepareStatement(instrumentQuery);
            ResultSet results = stmt.executeQuery();
            while (results.next()) {
                InstrumentGroup instrument = new InstrumentGroup();
                instrument.setId(results.getInt(1));
                instrument.setName(results.getNString(2));
                instrument.setStartDate(results.getDate(3).toString());
                instrument.setEndDate(results.getDate(4).toString());
                instruments.add(instrument);
            }
            con.close();
        } catch (SQLException sqle) {
            errorLabel.setText(sqle.getMessage());
        }
        masterData.setItems(instruments);

        TableColumn<InstrumentGroup, String> nameCol = new TableColumn<InstrumentGroup, String>("Megnevezés");
        nameCol.setCellValueFactory(new PropertyValueFactory("name"));

        TableColumn<InstrumentGroup, String> startCol = new TableColumn<InstrumentGroup, String>("Hatály kezdete");
        startCol.setCellValueFactory(new PropertyValueFactory("startDate"));

        TableColumn<InstrumentGroup, String> endCol = new TableColumn<InstrumentGroup, String>("Hatály vége");
        endCol.setCellValueFactory(new PropertyValueFactory("endDate"));
        masterData.getColumns().setAll(nameCol, startCol, endCol);
    }
}